package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.maszy.db.DBConnection;

public class RfidService {
	private static PreparedStatement pst;
	private static ResultSet rs = null;
	//销售汇总查询
	public List queryReport(Map m){
		
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "";
		if(m.get("card_no")!=null&&!"".equals(m.get("card_no"))){	
			//按卡号查询				
		}else{
			sql = " select a.maszy_doorstore_id,a.maszy_doorstore_name,(select sum(inputamt) from rfid where status=1 and doorstore_id=a.maszy_doorstore_id and saledate between ? and ?) as fill ,(select sum(inputamt) from rfid where status=2 and doorstore_id=a.maszy_doorstore_id and saledate between ? and ?) as pay from maszy_doorstore  a ";
		}try {		
			
				pst = con.getConnection().prepareStatement(sql);
				pst.setString(1, (String)m.get("time1")+" 00:00:00");
				pst.setString(2, (String)m.get("time2")+" 23:59:59");
				pst.setString(3, (String)m.get("time1")+" 00:00:00");
				pst.setString(4, (String)m.get("time2")+" 23:59:59");
			
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i));					
				}
				list.add(map);
				
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
		
	}
	
	public List queryRfid(Map m) {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "";	
		String query_and="";
		String query_order=" order by a.saledate";
		if(m.get("card_no")!=null&&!"".equals(m.get("card_no"))){		
			//按卡号查询	 
			query_and+=" and a.cardNo1='"+m.get("card_no")+"'";
		}
		if((Integer.parseInt((String) m.get("DoorStore_Id")))!=0){
			query_and+=" and a.DoorStore_Id="+m.get("DoorStore_Id");
		}
		if(m.get("time")!=null&&m.get("time")!=""){
			query_and+=" and a.saledate between '"+(String)m.get("time")+" 00:00:00'"+" and '"+(String)m.get("time")+" 23:59:59'";
		}
		if(m.get("status")!=null){
			query_and+=" and status= "+m.get("status");
		}
		sql = "SELECT b.maszy_doorstore_name,a.* FROM [Rfid] a ,maszy_doorstore b where a.doorstore_id=b.maszy_doorstore_id "+query_and +query_order;
		try {		
			pst = con.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					if("Status".equals(rsmd.getColumnName(i))){
						if((Integer)rs.getObject(i)==1){
							map.put(rsmd.getColumnName(i), "充值");
						}else if((Integer)rs.getObject(i)==2){
							map.put(rsmd.getColumnName(i), "消费");
						}
				
					}else{
						map.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					
				}
				list.add(map);
				
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
	
	/*
	 * SELECT a.Maszy_DoorStore_Id, a.Maszy_DoorStore_Name,sum()
FROM Maszy_DoorStore a LEFT OUTER JOIN
      Rfid b ON a.Maszy_DoorStore_Id = b.DoorStore_Id
	 * 
	 */
	public Map queryReportTotal(){
		DBConnection con = new DBConnection();
		Map<String, Object> map = new HashMap<String, Object>();
		 String sql="select count(maszy_membercard_id) count ,(select sum(inputamt)   from rfid where status=1  )  fill ,(select sum(inputamt)  from rfid where status=2 ) pay   from maszy_membercard";
		try {
			pst = con.getConnection().prepareStatement(sql);
		rs = pst.executeQuery();
		
		while (rs.next()) {
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				
					map.put(rsmd.getColumnName(i), rs.getObject(i));
					
				
			}
		}

	} catch (Exception e) {
		e.printStackTrace();
	} 
	finally {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}
	//查询未登记的非法卡号.
	con = new DBConnection();
	String sql2="select distinct(a.cardno1) cardno1 from rfid a  where a.cardno1 not in (select maszy_membercard_id from maszy_membercard)";
	String notregistercard="";
	try {
		pst = con.getConnection().prepareStatement(sql2);
		rs = pst.executeQuery();	
		while (rs.next()) {
			notregistercard+=rs.getString("cardno1")+"  ";
		}
	map.put("notregistercard", notregistercard);
	} catch (Exception e) {
		e.printStackTrace();
	} 
	finally {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}
//	查询所有消费金额大于充值金额的卡号.
	con = new DBConnection();
	String sql3="select distinct(a.cardno1),(select sum(inputamt) fill  from rfid where status=1 and cardno1=a.cardno1),(select sum(inputamt) charge  from rfid where status=2 and cardno1=a.cardno1)  from rfid a  where a.cardno1  in (select maszy_membercard_id from maszy_membercard)";
	String invaildcard="";
	try {
		pst = con.getConnection().prepareStatement(sql3);
		rs = pst.executeQuery();	
		while (rs.next()) {
			if(rs.getDouble(2)-rs.getDouble(3)<0){
				invaildcard+=rs.getString("cardno1")+"  ";
			}
		}
	map.put("invaildcard", invaildcard);
	} catch (Exception e) {
		e.printStackTrace();
	} 
	finally {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}
	return map;
	}
}
